using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.IO;
using System.Windows.Forms;

namespace 表单批量填写器
{
    /// <summary>
    /// Excel 导入辅助类 - 支持 CSV（原生）和 Excel（需要 Access Database Engine）
    /// </summary>
    public static class ExcelHelper
    {
        /// <summary>
        /// 从 Excel 或 CSV 文件导入表单数据
        /// </summary>
        /// <param name="filePath">文件路径</param>
        /// <returns>表单数据列表</returns>
        public static List<FormDataModel> ImportFromExcel(string filePath)
        {
            var dataList = new List<FormDataModel>();

            if (!File.Exists(filePath))
            {
                throw new FileNotFoundException("文件不存在", filePath);
            }

            string ext = Path.GetExtension(filePath).ToLower();

            // 优先使用 CSV 格式（无需依赖组件）
            if (ext == ".csv" || ext == ".txt")
            {
                return ImportFromCsv(filePath);
            }

            // Excel 格式需要 Access Database Engine
            try
            {
                // 构建连接字符串（支持 .xls 和 .xlsx）
                string connectionString;

                if (ext == ".xlsx")
                {
                    connectionString = $"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={filePath};Extended Properties='Excel 12.0 Xml;HDR=YES;IMEX=1'";
                }
                else if (ext == ".xls")
                {
                    connectionString = $"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={filePath};Extended Properties='Excel 8.0;HDR=YES;IMEX=1'";
                }
                else
                {
                    throw new ArgumentException("不支持的文件格式，请使用 .csv、.xls 或 .xlsx 文件");
                }

                using (OleDbConnection connection = new OleDbConnection(connectionString))
                {
                    connection.Open();

                    // 获取第一个工作表名称
                    DataTable sheets = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                    if (sheets == null || sheets.Rows.Count == 0)
                    {
                        throw new Exception("Excel 文件中没有找到工作表");
                    }

                    string sheetName = sheets.Rows[0]["TABLE_NAME"].ToString();

                    // 读取数据
                    string query = $"SELECT * FROM [{sheetName}]";
                    using (OleDbDataAdapter adapter = new OleDbDataAdapter(query, connection))
                    {
                        DataTable dataTable = new DataTable();
                        adapter.Fill(dataTable);

                        // 将数据转换为 FormDataModel
                        foreach (DataRow row in dataTable.Rows)
                        {
                            // 跳过空行
                            if (IsEmptyRow(row))
                                continue;

                            var formData = new FormDataModel
                            {
                                CustomerName = GetColumnValue(row, "客户姓名", "CustomerName", "custname"),
                                Telephone = GetColumnValue(row, "电话", "Telephone", "custtel"),
                                Email = GetColumnValue(row, "邮箱", "Email", "custemail"),
                                PizzaSize = GetColumnValue(row, "披萨尺寸", "PizzaSize", "size"),
                                PizzaToppings = GetColumnValue(row, "配料", "Toppings", "topping"),
                                DeliveryTime = GetColumnValue(row, "配送时间", "DeliveryTime", "delivery"),
                                Comments = GetColumnValue(row, "备注", "Comments", "comments")
                            };

                            if (formData.IsValid())
                            {
                                dataList.Add(formData);
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                // 如果是 Access Database Engine 错误，提供友好提示
                if (ex.Message.Contains("ACE.OLEDB") || ex.Message.Contains("Jet.OLEDB"))
                {
                    throw new Exception(
                        $"导入 Excel 文件失败: {ex.Message}\n\n" +
                        "解决方案：\n" +
                        "1. 使用 CSV 格式（推荐）：点击\"创建模板文件\"保存为 .csv\n" +
                        "2. 安装 Microsoft Access Database Engine：\n" +
                        "   64位：https://www.microsoft.com/download/details.aspx?id=54920\n" +
                        "   32位：https://www.microsoft.com/download/details.aspx?id=13255",
                        ex);
                }
                throw new Exception($"导入文件失败: {ex.Message}", ex);
            }

            return dataList;
        }

        /// <summary>
        /// 从 CSV 文件导入数据（原生支持，无需外部组件）
        /// </summary>
        private static List<FormDataModel> ImportFromCsv(string filePath)
        {
            var dataList = new List<FormDataModel>();

            try
            {
                // 读取所有行
                string[] lines = File.ReadAllLines(filePath, System.Text.Encoding.UTF8);

                if (lines.Length < 2)
                {
                    throw new Exception("CSV 文件内容为空或格式错误");
                }

                // 解析表头
                string[] headers = ParseCsvLine(lines[0]);
                var columnIndexes = new Dictionary<string, int>();

                // 建立列名到索引的映射
                for (int i = 0; i < headers.Length; i++)
                {
                    string header = headers[i].Trim().ToLower();
                    columnIndexes[header] = i;
                }

                // 解析数据行
                for (int i = 1; i < lines.Length; i++)
                {
                    string line = lines[i].Trim();
                    if (string.IsNullOrWhiteSpace(line))
                        continue;

                    string[] values = ParseCsvLine(line);

                    var formData = new FormDataModel
                    {
                        CustomerName = GetCsvValue(values, columnIndexes, "客户姓名", "customername", "custname"),
                        Telephone = GetCsvValue(values, columnIndexes, "电话", "telephone", "custtel"),
                        Email = GetCsvValue(values, columnIndexes, "邮箱", "email", "custemail"),
                        PizzaSize = GetCsvValue(values, columnIndexes, "披萨尺寸", "pizzasize", "size"),
                        PizzaToppings = GetCsvValue(values, columnIndexes, "配料", "toppings", "topping"),
                        DeliveryTime = GetCsvValue(values, columnIndexes, "配送时间", "deliverytime", "delivery"),
                        Comments = GetCsvValue(values, columnIndexes, "备注", "comments")
                    };

                    if (formData.IsValid())
                    {
                        dataList.Add(formData);
                    }
                }
            }
            catch (Exception ex)
            {
                throw new Exception($"导入 CSV 文件失败: {ex.Message}", ex);
            }

            return dataList;
        }

        /// <summary>
        /// 解析 CSV 行（处理逗号和引号）
        /// </summary>
        private static string[] ParseCsvLine(string line)
        {
            var result = new List<string>();
            bool inQuotes = false;
            var currentField = new System.Text.StringBuilder();

            for (int i = 0; i < line.Length; i++)
            {
                char c = line[i];

                if (c == '"')
                {
                    inQuotes = !inQuotes;
                }
                else if (c == ',' && !inQuotes)
                {
                    result.Add(currentField.ToString().Trim());
                    currentField.Clear();
                }
                else
                {
                    currentField.Append(c);
                }
            }

            result.Add(currentField.ToString().Trim());
            return result.ToArray();
        }

        /// <summary>
        /// 从 CSV 数组中获取值
        /// </summary>
        private static string GetCsvValue(string[] values, Dictionary<string, int> columnIndexes, params string[] possibleNames)
        {
            foreach (var name in possibleNames)
            {
                string lowerName = name.ToLower();
                if (columnIndexes.ContainsKey(lowerName))
                {
                    int index = columnIndexes[lowerName];
                    if (index < values.Length)
                    {
                        return values[index].Trim();
                    }
                }
            }
            return string.Empty;
        }

        /// <summary>
        /// 获取列值（支持多种列名）
        /// </summary>
        private static string GetColumnValue(DataRow row, params string[] columnNames)
        {
            foreach (var columnName in columnNames)
            {
                if (row.Table.Columns.Contains(columnName))
                {
                    var value = row[columnName];
                    if (value != null && value != DBNull.Value)
                    {
                        return value.ToString().Trim();
                    }
                }
            }
            return string.Empty;
        }

        /// <summary>
        /// 判断是否为空行
        /// </summary>
        private static bool IsEmptyRow(DataRow row)
        {
            foreach (var item in row.ItemArray)
            {
                if (item != null && item != DBNull.Value && !string.IsNullOrWhiteSpace(item.ToString()))
                {
                    return false;
                }
            }
            return true;
        }

        /// <summary>
        /// 创建 Excel 模板文件（供用户填写数据）
        /// </summary>
        public static void CreateExcelTemplate(string filePath)
        {
            try
            {
                // 创建一个简单的 CSV 模板（兼容 Excel）
                var csvContent = "客户姓名,电话,邮箱,披萨尺寸,配料,配送时间,备注\n" +
                                "张三,13800138000,zhangsan@example.com,medium,bacon;cheese,12:00,请送到前台\n" +
                                "李四,13900139000,lisi@example.com,large,cheese;mushroom,13:30,无备注\n" +
                                "王五,13700137000,wangwu@example.com,small,onion,14:00,不要洋葱";

                File.WriteAllText(filePath, csvContent, System.Text.Encoding.UTF8);
            }
            catch (Exception ex)
            {
                throw new Exception($"创建 Excel 模板失败: {ex.Message}", ex);
            }
        }
    }
}
